import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
import '../models/restaurant.dart';
import '../models/product.dart';
import '../models/product_template.dart';
import '../models/bill.dart';
import '../models/bill_item.dart';


class DatabaseHelper {


  // 单例模式，确保只有一个数据库实例
  static final DatabaseHelper _instance = DatabaseHelper._internal();
  factory DatabaseHelper() => _instance;
  DatabaseHelper._internal();

  static Database? _database;

    // 数据库版本升级到5，添加账单相关表
  final String _databaseName = 'delivery_accounting.db';
  final int _databaseVersion = 8; 

  // 获取数据库实例
  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDatabase();
    return _database!;
  }

  // 初始化数据库
  Future<Database> _initDatabase() async {
    String path = join(await getDatabasesPath(), _databaseName);
    return await openDatabase(
      path,
      version: _databaseVersion,
      onCreate: _onCreate,
      onUpgrade: _onUpgrade,
    );
  }

  // 创建所有表
  Future<void> _onCreate(Database db, int version) async {
    // 创建饭店表
    await db.execute('''
      CREATE TABLE restaurants(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        contact TEXT,
        phone TEXT,
        address TEXT
      )
    ''');

    // 创建商品表
    await db.execute('''
      CREATE TABLE products(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        restaurant_id INTEGER NOT NULL,
        name TEXT NOT NULL,
        spec TEXT NOT NULL,
        current_price REAL NOT NULL,
        FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE
      )
    ''');

    // 创建商品模板表（包含分类字段）
    await db.execute('''
      CREATE TABLE IF NOT EXISTS product_templates(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL UNIQUE,
        default_spec TEXT NOT NULL,
        default_price REAL NOT NULL,
        category TEXT NOT NULL DEFAULT '其他'
      )
    ''');
    // 创建账单表
    await db.execute('''
      CREATE TABLE bills(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        restaurant_id INTEGER NOT NULL,
        restaurant_name TEXT NOT NULL,
        total_amount REAL NOT NULL,
        bill_date INTEGER NOT NULL,
        status TEXT NOT NULL DEFAULT 'pending',
        notes TEXT,
        created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL,
        FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE
      )
    ''');

    // 创建账单项表（包含价格快照）
    await db.execute('''
      CREATE TABLE bill_items(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        bill_id INTEGER NOT NULL,
        product_id INTEGER NOT NULL,
        product_name TEXT NOT NULL,
        product_spec TEXT NOT NULL,
        unit_price REAL NOT NULL,
        quantity INTEGER NOT NULL,
        subtotal REAL NOT NULL,
        FOREIGN KEY (bill_id) REFERENCES bills (id) ON DELETE CASCADE,
        FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE
      )
    ''');

    // 插入默认模板数据
    await _insertDefaultTemplates(db);
    
    print('✅ 数据库表创建成功');
  }

  // 数据库升级处理
  Future<void> _onUpgrade(Database db, int oldVersion, int newVersion) async {
    print('🔄 数据库升级: $oldVersion -> $newVersion');
    
    if (oldVersion < 2) {
      // 从版本1升级到版本2：添加商品表
      await db.execute('''
        CREATE TABLE IF NOT EXISTS products(
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          restaurant_id INTEGER NOT NULL,
          name TEXT NOT NULL,
          spec TEXT NOT NULL,
          current_price REAL NOT NULL,
          FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE
        )
      ''');
      print('✅ 商品表创建成功');
    }
    
    if (oldVersion < 3) {
      // 从版本2升级到版本3：添加商品模板表
      await db.execute('''
        CREATE TABLE IF NOT EXISTS product_templates(
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          name TEXT NOT NULL UNIQUE,
          default_spec TEXT NOT NULL,
          default_price REAL NOT NULL
        )
      ''');
      
      // 插入默认模板数据
      await _insertDefaultTemplates(db);
      print('✅ 商品模板表创建成功');
    }
    
    if (oldVersion < 4) {
      // 从版本3升级到版本4：添加分类字段
      try {
        await db.execute('ALTER TABLE product_templates ADD COLUMN category TEXT NOT NULL DEFAULT "其他"');
        print('✅ 商品模板表添加分类字段成功');
        
        // 更新现有模板的分类信息
        await _updateTemplateCategories(db);
      } catch (e) {
        print('添加分类字段失败（可能已存在）: $e');
      }
    }

        if (oldVersion < 5) {
      // 从版本4升级到版本5：添加账单相关表
      await db.execute('''
        CREATE TABLE IF NOT EXISTS bills(
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          restaurant_id INTEGER NOT NULL,
          restaurant_name TEXT NOT NULL,
          total_amount REAL NOT NULL,
          bill_date INTEGER NOT NULL,
          status TEXT NOT NULL DEFAULT 'pending',
          notes TEXT,
          created_at INTEGER NOT NULL,
          updated_at INTEGER NOT NULL,
          FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE
        )
      ''');

      await db.execute('''
        CREATE TABLE IF NOT EXISTS bill_items(
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          bill_id INTEGER NOT NULL,
          product_id INTEGER NOT NULL,
          product_name TEXT NOT NULL,
          product_spec TEXT NOT NULL,
          unit_price REAL NOT NULL,
          quantity INTEGER NOT NULL,
          subtotal REAL NOT NULL,
          FOREIGN KEY (bill_id) REFERENCES bills (id) ON DELETE CASCADE,
          FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE
        )
      ''');
      
      print('✅ 账单相关表创建成功');
    }
    if (oldVersion < 6) {
      // 从版本5升级到版本6：为bills表添加modification_history列
      await db.execute('''
        ALTER TABLE bills ADD COLUMN modification_history TEXT
      ''');
      print('✅ 为bills表添加modification_history列成功');
    }
    if (oldVersion < 7) {
      // 从版本6升级到版本7：为bills表添加custom_date列
      try {
        await db.execute('ALTER TABLE bills ADD COLUMN custom_date INTEGER');
        print('✅ 为bills表添加custom_date列成功');
        
        // 将现有的bill_date数据复制到custom_date
        await db.execute('UPDATE bills SET custom_date = bill_date');
        print('✅ 现有账单数据custom_date初始化成功');
      } catch (e) {
        print('添加custom_date列失败（可能已存在）: $e');
      }
    }
    // 在 _onUpgrade 方法中添加版本8的升级逻辑
    if (oldVersion < 8) {
      // 从版本7升级到版本8：添加价格快照表
      try {
        await db.execute('''
          CREATE TABLE IF NOT EXISTS price_snapshots (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            bill_id INTEGER NOT NULL,
            product_id INTEGER NOT NULL,
            snapshot_price REAL NOT NULL,
            created_at INTEGER NOT NULL,
            FOREIGN KEY (bill_id) REFERENCES bills (id) ON DELETE CASCADE,
            FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE,
            UNIQUE(bill_id, product_id)
          )
        ''');
        print('✅ 价格快照表创建成功');
        
        // 为现有账单数据创建价格快照
        await _migratePriceSnapshots(db);
      } catch (e) {
        print('创建价格快照表失败: $e');
      }
    }
  }



// ---------------------------------------------------------------------------------------------------

    // 添加价格快照迁移方法
  Future<void> _migratePriceSnapshots(Database db) async {
    try {
      // 从现有账单项中提取价格信息创建快照
      final billItems = await db.query('bill_items');
      final batch = db.batch();
      
      for (var item in billItems) {
        batch.insert('price_snapshots', {
          'bill_id': item['bill_id'],
          'product_id': item['product_id'],
          'snapshot_price': item['unit_price'],
          'created_at': DateTime.now().millisecondsSinceEpoch,
        });
      }
      
      await batch.commit();
      print('✅ 价格快照数据迁移成功');
    } catch (e) {
      print('价格快照迁移失败: $e');
    }
  }


  // 价格快照相关操作
Future<int> createPriceSnapshot(int billId, int productId, double price) async {
  final db = await database;
  return await db.insert('price_snapshots', {
    'bill_id': billId,
    'product_id': productId,
    'snapshot_price': price,
    'created_at': DateTime.now().millisecondsSinceEpoch,
  });
}

  Future<double?> getPriceSnapshot(int billId, int productId) async {
    final db = await database;
    final List<Map<String, dynamic>> result = await db.query(
      'price_snapshots',
      where: 'bill_id = ? AND product_id = ?',
      whereArgs: [billId, productId],
    );
    
    if (result.isNotEmpty) {
      return result.first['snapshot_price'] as double;
    }
    return null;
  }

  // 事务性账单创建（包含价格快照）
  Future<int> createBillWithPriceProtection(Bill bill, List<BillItem> items) async {
    final db = await database;
    
    return await db.transaction((txn) async {
      // 1. 插入账单
      final billId = await txn.insert('bills', bill.toMap());
      
      // 2. 插入账单项并创建价格快照
      for (var item in items) {
        final itemMap = item.toMap();
        itemMap['bill_id'] = billId;
        await txn.insert('bill_items', itemMap);
        
        // 创建价格快照
        await txn.insert('price_snapshots', {
          'bill_id': billId,
          'product_id': item.productId,
          'snapshot_price': item.unitPrice,
          'created_at': DateTime.now().millisecondsSinceEpoch,
        });
      }
      
      return billId;
    });
  }

 // 更新现有模板的分类信息
  Future<void> _updateTemplateCategories(Database db) async {
    final categoryUpdates = [
      {'name': '猪肉', 'category': '肉类'},
      {'name': '牛肉', 'category': '肉类'},
      {'name': '鸡肉', 'category': '肉类'},
      {'name': '鸡蛋', 'category': '蛋类'},
      {'name': '大米', 'category': '粮油'},
      {'name': '食用油', 'category': '粮油'},
      {'name': '蔬菜', 'category': '蔬菜'},
      {'name': '水果', 'category': '水果'},
      {'name': '饮料', 'category': '饮品'},
      {'name': '啤酒', 'category': '饮品'},
    ];

    final batch = db.batch();
    for (var update in categoryUpdates) {
      batch.update(
        'product_templates',
        {'category': update['category']},
        where: 'name = ?',
        whereArgs: [update['name']],
      );
    }
    await batch.commit();
    print('✅ 模板分类信息更新成功');
  }

  // 插入默认模板数据
  Future<void> _insertDefaultTemplates(Database db) async {
    // 首先检查是否已有数据
    final count = await db.rawQuery('SELECT COUNT(*) as count FROM product_templates');
    final int templateCount = count.first['count'] as int;
    
    if (templateCount > 0) {
      print('✅ 模板数据已存在，跳过插入');
      return;
    }

    final defaultTemplates = [
      ProductTemplate(name: '猪肉', defaultSpec: '斤', defaultPrice: 25.0, category: '肉类'),
      ProductTemplate(name: '牛肉', defaultSpec: '斤', defaultPrice: 45.0, category: '肉类'),
      ProductTemplate(name: '鸡肉', defaultSpec: '斤', defaultPrice: 18.0, category: '肉类'),
      ProductTemplate(name: '鸡蛋', defaultSpec: '箱', defaultPrice: 120.0, category: '蛋类'),
      ProductTemplate(name: '大米', defaultSpec: '袋', defaultPrice: 85.0, category: '粮油'),
      ProductTemplate(name: '食用油', defaultSpec: '桶', defaultPrice: 65.0, category: '粮油'),
      ProductTemplate(name: '蔬菜', defaultSpec: '斤', defaultPrice: 8.0, category: '蔬菜'),
      ProductTemplate(name: '水果', defaultSpec: '斤', defaultPrice: 12.0, category: '水果'),
      ProductTemplate(name: '饮料', defaultSpec: '箱', defaultPrice: 45.0, category: '饮品'),
      ProductTemplate(name: '啤酒', defaultSpec: '箱', defaultPrice: 55.0, category: '饮品'),
    ];

    final batch = db.batch();
    for (var template in defaultTemplates) {
      batch.insert('product_templates', template.toMap());
    }
    await batch.commit();
    
    print('✅ 默认商品模板插入成功');
  }
  // 饭店表操作
  // 添加饭店
  Future<int> insertRestaurant(Restaurant restaurant) async {
    final db = await database;
    
    // 🧪 测试代码：模拟数据库失败
    // 当饭店名称包含"测试失败"时抛出异常
    if (restaurant.name.contains('测试失败')) {
      await Future.delayed(const Duration(seconds: 1)); // 模拟延迟
      throw Exception('模拟数据库连接失败：无法访问数据库文件');
    }
    
    // 🧪 测试代码：模拟唯一约束冲突
    if (restaurant.name.contains('重复名称')) {
      await Future.delayed(const Duration(seconds: 1));
      throw Exception('UNIQUE constraint failed: restaurants.name');
    }
    
    return await db.insert('restaurants', restaurant.toMap());
  }

  // 获取所有饭店
  Future<List<Restaurant>> getRestaurants() async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query('restaurants');
    return List.generate(maps.length, (i) {
      return Restaurant.fromMap(maps[i]);
    });
  }

  // 更新饭店
  Future<int> updateRestaurant(Restaurant restaurant) async {
    final db = await database;
    return await db.update(
      'restaurants',
      restaurant.toMap(),
      where: 'id = ?',
      whereArgs: [restaurant.id],
    );
  }

  // 删除饭店
  Future<int> deleteRestaurant(int id) async {
    final db = await database;
    return await db.delete(
      'restaurants',
      where: 'id = ?',
      whereArgs: [id],
    );
  }


  
  // 商品表操作
  // 添加商品
  Future<int> insertProduct(Product product) async {
    final db = await database;
    return await db.insert('products', product.toMap());
  }

  // 获取某个饭店的所有商品
  Future<List<Product>> getProductsByRestaurant(int restaurantId) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'products',
      where: 'restaurant_id = ?',
      whereArgs: [restaurantId],
    );
    return List.generate(maps.length, (i) {
      return Product.fromMap(maps[i]);
    });
  }

  // 更新商品
  Future<int> updateProduct(Product product) async {
    final db = await database;
    return await db.update(
      'products',
      product.toMap(),
      where: 'id = ?',
      whereArgs: [product.id],
    );
  }

  // 删除商品
  Future<int> deleteProduct(int id) async {
    final db = await database;
    return await db.delete(
      'products',
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  // 检查商品名称是否重复（在同一饭店内）
  Future<bool> isProductNameDuplicate(int restaurantId, String name, {int? excludeId}) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'products',
      where: 'restaurant_id = ? AND name = ? AND id != ?',
      whereArgs: [restaurantId, name, excludeId ?? 0],
    );
    return maps.isNotEmpty;
  }
  // 重置数据库（开发测试用）
  Future<void> resetDatabase() async {
    final db = await database;
    await db.close();
    _database = null;
    
    String path = join(await getDatabasesPath(), _databaseName);
    await deleteDatabase(path);
    
    print('🗑️ 数据库已重置');
    _database = await _initDatabase();
  }
  
  // 检查表是否存在
  Future<bool> checkTableExists(String tableName) async {
    final db = await database;
    try {
      final result = await db.rawQuery(
        "SELECT name FROM sqlite_master WHERE type='table' AND name='$tableName'"
      );
      return result.isNotEmpty;
    } catch (e) {
      print('检查表存在失败: $e');
      return false;
    }
  }
    // 商品模板表操作
  // 添加商品模板
  Future<int> insertProductTemplate(ProductTemplate template) async {
    final db = await database;
    return await db.insert('product_templates', template.toMap());
  }
  // 添加获取所有分类的方法
  Future<List<String>> getTemplateCategories() async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'product_templates',
      columns: ['category'],
      distinct: true,
    );
    return maps.map((map) => map['category'] as String).toList();
  }

  // 获取所有商品模板
  Future<List<ProductTemplate>> getProductTemplates() async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query('product_templates');
    return List.generate(maps.length, (i) {
      return ProductTemplate.fromMap(maps[i]);
    });
  }

  // 更新商品模板
  Future<int> updateProductTemplate(ProductTemplate template) async {
    final db = await database;
    return await db.update(
      'product_templates',
      template.toMap(),
      where: 'id = ?',
      whereArgs: [template.id],
    );
  }

  // 删除商品模板
  Future<int> deleteProductTemplate(int id) async {
    final db = await database;
    return await db.delete(
      'product_templates',
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  // 从模板创建商品
  Future<void> createProductsFromTemplates(int restaurantId, List<ProductTemplate> templates) async {
    final db = await database;
    final batch = db.batch();
    
    for (var template in templates) {
      final product = Product(
        restaurantId: restaurantId,
        name: template.name,
        spec: template.defaultSpec,
        currentPrice: template.defaultPrice,
      );
      batch.insert('products', product.toMap());
    }
    
    await batch.commit();
  }

  // 检查模板名称是否重复
  Future<bool> isTemplateNameDuplicate(String name, {int? excludeId}) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'product_templates',
      where: 'name = ? AND id != ?',
      whereArgs: [name, excludeId ?? 0],
    );
    return maps.isNotEmpty;
  }
 
   // 插入新账单
  Future<int> insertBill(Bill bill) async {
    final db = await database;
    final now = DateTime.now().millisecondsSinceEpoch;
    
    final Map<String, dynamic> billMap = bill.toMap();
    billMap['created_at'] = now;
    billMap['updated_at'] = now;
    
    return await db.insert('bills', billMap);
  }

  // 获取所有账单（按时间倒序）
  Future<List<Bill>> getBills({String? status}) async {
    final db = await database;
    
    String whereClause = '1=1';
    List<dynamic> whereArgs = [];
    
    if (status != null) {
      whereClause += ' AND status = ?';
      whereArgs.add(status);
    }
    
    final List<Map<String, dynamic>> maps = await db.query(
      'bills',
      where: whereClause,
      whereArgs: whereArgs,
      orderBy: 'bill_date DESC',
    );
    
    return List.generate(maps.length, (i) {
      return Bill.fromMap(maps[i]);
    });
  }

  // 根据ID获取账单
  Future<Bill?> getBillById(int id) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'bills',
      where: 'id = ?',
      whereArgs: [id],
    );
    
    if (maps.isNotEmpty) {
      return Bill.fromMap(maps.first);
    }
    return null;
  }

  // 获取账单的账单项
  Future<List<BillItem>> getBillItemsByBillId(int billId) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'bill_items',
      where: 'bill_id = ?',
      whereArgs: [billId],
      orderBy: 'id ASC',
    );
    
    return List.generate(maps.length, (i) {
      return BillItem.fromMap(maps[i]);
    });
  }

  // // 更新账单（支持修改）
  // Future<int> updateBill(Bill bill) async {
  //   final db = await database;
  //   final now = DateTime.now().millisecondsSinceEpoch;
    
  //   final Map<String, dynamic> billMap = bill.toMap();
  //   billMap['updated_at'] = now;
    
  //   return await db.update(
  //     'bills',
  //     billMap,
  //     where: 'id = ?',
  //     whereArgs: [bill.id],
  //   );
  // }

  // 更新账单备注
  Future<int> updateBillNotes(int billId, String notes) async {
    final db = await database;
    final now = DateTime.now().millisecondsSinceEpoch;
    
    return await db.update(
      'bills',
      {
        'notes': notes,
        'updated_at': now,
      },
      where: 'id = ?',
      whereArgs: [billId],
    );
  }

  // 删除账单（会级联删除账单项）
  Future<int> deleteBill(int id) async {
    final db = await database;
    return await db.delete(
      'bills',
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  // 统计方法

  // 获取指定时间范围内的账单统计
  Future<Map<String, dynamic>> getBillsStatistics(DateTime startDate, DateTime endDate) async {
    final db = await database;
    
    final startMillis = startDate.millisecondsSinceEpoch;
    final endMillis = endDate.millisecondsSinceEpoch;
    
    // 总销售额
    final totalResult = await db.rawQuery('''
      SELECT COUNT(*) as bill_count, COALESCE(SUM(total_amount), 0) as total_revenue
      FROM bills 
      WHERE bill_date BETWEEN ? AND ? AND status = 'paid'
    ''', [startMillis, endMillis]);
    
    // 各饭店销售额
    final restaurantResult = await db.rawQuery('''
      SELECT restaurant_name, COUNT(*) as bill_count, SUM(total_amount) as revenue
      FROM bills 
      WHERE bill_date BETWEEN ? AND ? AND status = 'paid'
      GROUP BY restaurant_id, restaurant_name
      ORDER BY revenue DESC
    ''', [startMillis, endMillis]);
    
    // 热销商品
    final productResult = await db.rawQuery('''
      SELECT 
        bi.product_name,
        SUM(bi.quantity) as total_quantity,
        SUM(bi.subtotal) as total_revenue,
        AVG(bi.unit_price) as avg_price
      FROM bill_items bi
      JOIN bills b ON bi.bill_id = b.id
      WHERE b.bill_date BETWEEN ? AND ? AND b.status = 'paid'
      GROUP BY bi.product_name
      ORDER BY total_quantity DESC
      LIMIT 10
    ''', [startMillis, endMillis]);
    
    return {
      'total': totalResult.first,
      'restaurants': restaurantResult,
      'products': productResult,
    };
  }

  // 获取今日账单
  Future<List<Bill>> getTodayBills() async {
    final now = DateTime.now();
    final startOfDay = DateTime(now.year, now.month, now.day);
    final endOfDay = DateTime(now.year, now.month, now.day, 23, 59, 59);
    
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'bills',
      where: 'bill_date BETWEEN ? AND ?',
      whereArgs: [startOfDay.millisecondsSinceEpoch, endOfDay.millisecondsSinceEpoch],
      orderBy: 'bill_date DESC',
    );
    
    return List.generate(maps.length, (i) {
      return Bill.fromMap(maps[i]);
    });
  }

  // 获取待处理账单
  Future<List<Bill>> getPendingBills() async {
    return await getBills(status: 'pending');
  }

  // 检查账单是否可删除（只有待处理状态的账单可以删除）
  Future<bool> canDeleteBill(int billId) async {
    final bill = await getBillById(billId);
    return bill != null && bill.status == 'pending';
  }
  // 在 DatabaseHelper 类中添加以下方法：


// 获取账单统计信息
Future<Map<String, dynamic>> getBillStatistics(DateTime startDate, DateTime endDate) async {
  final db = await database;
  final startMillis = startDate.millisecondsSinceEpoch;
  final endMillis = endDate.millisecondsSinceEpoch;

  // 总销售额
  final totalSalesResult = await db.rawQuery('''
    SELECT SUM(total_amount) as total_sales 
    FROM bills 
    WHERE bill_date BETWEEN ? AND ? AND status = 'paid'
  ''', [startMillis, endMillis]);

  // 账单数量
  final billCountResult = await db.rawQuery('''
    SELECT COUNT(*) as bill_count 
    FROM bills 
    WHERE bill_date BETWEEN ? AND ?
  ''', [startMillis, endMillis]);

  // 各状态账单数量
  final statusCountResult = await db.rawQuery('''
    SELECT status, COUNT(*) as count 
    FROM bills 
    WHERE bill_date BETWEEN ? AND ?
    GROUP BY status
  ''', [startMillis, endMillis]);

  // 热销商品
  final topProductsResult = await db.rawQuery('''
    SELECT 
      product_name,
      SUM(quantity) as total_quantity,
      SUM(subtotal) as total_revenue
    FROM bill_items 
    JOIN bills ON bill_items.bill_id = bills.id
    WHERE bills.bill_date BETWEEN ? AND ? AND bills.status = 'paid'
    GROUP BY product_name
    ORDER BY total_revenue DESC
    LIMIT 10
  ''', [startMillis, endMillis]);

  return {
    'total_sales': totalSalesResult.first['total_sales'] ?? 0.0,
    'bill_count': billCountResult.first['bill_count'] ?? 0,
    'status_counts': Map.fromEntries(
      statusCountResult.map((row) => MapEntry(row['status'] as String, row['count']))
    ),
    'top_products': topProductsResult,
    'start_date': startDate,
    'end_date': endDate,
  };
}
  // 获取账单修改历史
  Future<List<BillModification>> getBillModificationHistory(int billId) async {
    final db = await database;
    // 这里可以从单独的修改历史表查询，或者从 bills 表的 modification_history 字段解析
    // 简化实现：直接从 bills 表获取
    final List<Map<String, dynamic>> maps = await db.query(
      'bills',
      columns: ['modification_history'],
      where: 'id = ?',
      whereArgs: [billId],
    );
    
    if (maps.isNotEmpty && maps.first['modification_history'] != null) {
      final historyData = maps.first['modification_history'] as List;
      return historyData.map((m) => BillModification.fromMap(m)).toList();
    }
    
    return [];
  }
  
// 更新账单状态
Future<int> updateBillStatus(int billId, String status) async {
  final db = await database;
  final now = DateTime.now().millisecondsSinceEpoch;
  
  return await db.update(
    'bills',
    {
      'status': status,
      'updated_at': now,
    },
    where: 'id = ?',
    whereArgs: [billId],
  );
}

// 确保以下方法存在
Future<int> insertBillItem(BillItem billItem) async {
  final db = await database;
  return await db.insert('bill_items', billItem.toMap());
}
// 更新账单（支持修改）
Future<int> updateBill(Bill bill) async {
  final db = await database;
  final now = DateTime.now().millisecondsSinceEpoch;
  
  final Map<String, dynamic> billMap = bill.toMap();
  billMap['updated_at'] = now;
  
  return await db.update(
    'bills',
    billMap,
    where: 'id = ?',
    whereArgs: [bill.id],
  );
}

// 更新账单项
Future<int> updateBillItem(BillItem billItem) async {
  final db = await database;
  return await db.update(
    'bill_items',
    billItem.toMap(),
    where: 'id = ?',
    whereArgs: [billItem.id],
  );
}

// 删除账单项
Future<int> deleteBillItem(int billItemId) async {
  final db = await database;
  return await db.delete(
    'bill_items',
    where: 'id = ?',
    whereArgs: [billItemId],
  );
}







  // 关闭数据库
  Future<void> close() async {
    final db = await database;
    db.close();
  }
}